package cn.dida.dao;

import java.util.Date;
import java.util.HashMap;
import java.util.List;

import org.apache.commons.lang3.ArrayUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import cn.dida.model.Pageable;
import cn.dida.model.Product;

/**
 * 产品管理 数据交互类
 * @author weiwei
 */
@Repository
public class ProductDao extends DaoBase {

	/**
	 * 根据产品id和公司id获取产品信息
	 * @param id 产品id
	 * @param companyId	公司id
	 * @return Product 产品信息
	 */
    public Product getById$Company(Integer id, Integer companyId) {
        String sql = "select * from product where id = ? and company_id = ?  limit 1";
        try {
            Product product = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Product>(Product.class), id, companyId);
            return product;
        } catch (DataAccessException e) {
            return null;
        }
    }
    /**
     * 根据产品名称和公司id获取产品信息
     * @param name 产品名称
     * @param companyId 公司id
     * @return Product 产品信息
     */
    public Product getByName$Company(String name, Integer companyId) {
        String sql = "select * from product where name = ? and company_id = ?  limit 1";
        try {
            Product product = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Product>(Product.class), name, companyId);
            return product;
        } catch (DataAccessException e) {
            return null;
        }
    }

    /**
     * 根据条码和公司id获取产品信息
     * @param barcode 产品条码
     * @param companyId 公司id
     * @return Product 产品信息
     */
    public Product getByBarcode$Company(String barcode, Integer companyId) {
        String sql = "select * from product where barcode = ? and company_id = ?  limit 1";
        try {
            Product product = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Product>(Product.class), barcode, companyId);
            return product;
        } catch (DataAccessException e) {
            return null;
        }
    }

    /**
     * 根据编号与公司id获取产品信息
     * @param number 产品编号
     * @param companyId 公司id
     * @return Product 产品信息
     */
    public Product getByNum$Company(String number, Integer companyId) {
        String sql = "select * from product where number = ? and company_id = ?  limit 1";
        try {
            Product product = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<Product>(Product.class), number, companyId);
            return product;
        } catch (DataAccessException e) {
            return null;
        }
    }

    /**
     * 产品列表
     * @param companyId 公司id
     * @param keyword 查询条件
     * @param state 状态：1使用中，2已封存
     * @param page 分页游标
     * @return Pageable<Product> 产品分页列表
     */
    public Pageable<Product> query(Integer companyId, String keyword, Integer state, int page) {
        Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("select count(1) from product prd  join brand brd on prd.brand_id = brd.id left join staff stf on prd.create_staff_id = stf.id join pbc on prd.pbc_id = pbc.id");
        StringBuffer sqlQueryResult = new StringBuffer("select prd.*,brd.name 'brandName',stf.account 'createStaffAccount',pbc.pcount 'pbcPcount' from product prd  join brand brd on prd.brand_id = brd.id left join staff stf on prd.create_staff_id = stf.id  join pbc on prd.pbc_id = pbc.id");

        StringBuffer sqlWhere = new StringBuffer(" where prd.company_id = ? and (prd.name like ? or prd.number = ? or prd.barcode = ? or brd.name like ?)");
        args = ArrayUtils.add(args, companyId);
        args = ArrayUtils.add(args, "%" + keyword + "%");
        args = ArrayUtils.add(args, keyword);
        args = ArrayUtils.add(args, keyword);
        args = ArrayUtils.add(args, "%" + keyword + "%");
        if (state != null) {
            sqlWhere.append(" and prd.state = ?");
            args = ArrayUtils.add(args, state);
        }

        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  order by prd.create_time desc limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<Product> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(Product.class));
        return new Pageable<>(page, getPageSize(), resultTotal, result);
    }
    /**
     * 产品列表（用于数据导出）
     * @param keyword 查询条件，关键字：名称、编号、条码、品牌
     * @param state 产品状态：1使用中，2已封存
     * @return List<Product> 产品列表
     * @date 2016-12-07 14:37
     * */
    public List<Product> queryForExport(Integer companyId, String keyword, Integer state) {
    	Object[] args = {};
    	StringBuffer sqlQueryResult = new StringBuffer("select prd.*,brd.name 'brandName',stf.account 'createStaffAccount',pbc.pcount 'pbcPcount' from product prd left join brand brd on prd.brand_id = brd.id left join staff stf on prd.create_staff_id = stf.id left join pbc on prd.pbc_id = pbc.id");
    	
    	StringBuffer sqlWhere = new StringBuffer(" where prd.company_id = ? and (prd.name like ? or prd.number = ? or prd.barcode = ? or brd.name like ?)");
    	args = ArrayUtils.add(args, companyId);
    	args = ArrayUtils.add(args, "%" + keyword + "%");
    	args = ArrayUtils.add(args, keyword);
    	args = ArrayUtils.add(args, keyword);
    	args = ArrayUtils.add(args, "%" + keyword + "%");
    	if (state != null) {
    		sqlWhere.append(" and prd.state = ?");
    		args = ArrayUtils.add(args, state);
    	}
    	
    	// 查询记录
    	sqlQueryResult.append(sqlWhere).append("  order by prd.create_time desc ");
    	List<Product> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(Product.class));
    	return result;
    }

    /**
     * 新增产品
     * @param product 产品实体信息
     * @return int 受影响的记录行数
     */
    public int insert(Product product) {
        String sql = "insert into product(company_id, name, number, barcode, price, brand_id, pbc_id, pictures, create_time, state, create_staff_id) values (:company_id, :name, :number, :barcode, :price, :brand_id, :pbc_id, :pictures, :create_time, :state, :create_staff_id)";
        HashMap<String, Object> paramMap = new HashMap<>();
        paramMap.put("company_id", product.getCompanyId());
        paramMap.put("name", product.getName());
        paramMap.put("number", product.getNumber());
        paramMap.put("barcode", product.getBarcode());
        paramMap.put("price", product.getPrice());
        paramMap.put("brand_id", product.getBrandId());
        paramMap.put("pbc_id", product.getPbcId());
        paramMap.put("state", product.getState());
        paramMap.put("pictures", product.getPictures());
        paramMap.put("create_time", new Date());
        paramMap.put("create_staff_id", product.getCreateStaffId());
        return namedParameterJdbcTemplate.update(sql, paramMap);
    }

    /**
     * 更新产品
     * @param product 产品实体信息
     * @return int 受影响的记录行数
     */
    public int update(Product product) {
        String sql = "update product set name=:name, number=:number, barcode=:barcode, price=:price, brand_id=:brand_id, pbc_id=:pbc_id, pictures=:pictures, state=:state where id=:id and company_id=:company_id";
        HashMap<String, Object> paramMap = new HashMap<>();
        paramMap.put("name", product.getName());
        paramMap.put("number", product.getNumber());
        paramMap.put("barcode", product.getBarcode());
        paramMap.put("price", product.getPrice());
        paramMap.put("brand_id", product.getBrandId());
        paramMap.put("pbc_id", product.getPbcId());
        paramMap.put("state", product.getState());
        paramMap.put("pictures", product.getPictures());
        paramMap.put("id", product.getId());
        paramMap.put("company_id", product.getCompanyId());
        return namedParameterJdbcTemplate.update(sql, paramMap);
    }

    /**
     * 删除产品
     * @param companyId 公司id
     * @param number 产品编号
     * @return int 受影响的记录行数
     */
    public int delete(Integer companyId, String number) {
        String sql = "delete from product where company_id = ? and number = ?";
        return jdbcTemplate.update(sql, companyId, number);
    }

    /**
     * 选择商品功能：根据id获取商品列表
     * @param ids 商品id
     * @return List<Product> 商品列表
     * @date 2016-11-17 15:24
     */
    public List<Product> getProductListByIds(String ids) {
        Object[] args = {};
        String sql = "select * from product where id in(?)";
        args = ArrayUtils.add(args, ids);
        List<Product> productList = jdbcTemplate.query(sql, args, new BeanPropertyRowMapper<>(Product.class));
        return productList;
    }

    /**
     * 选择商品功能：根据活动id获取商品列表
     * @param activityId 活动id
     * @return List<Product> 商品列表
     * @date 2016-11-17 15:26
     */
    public List<Product> getProductsByActivityId(String activityId) {
        Object[] args = {};
        String sql = "select p.*,b.name 'brandName' from product p left join brand b on p.brand_id = b.id  where p.id in(select product_id from activity_product where activity_id=?)";
        args = ArrayUtils.add(args, activityId);
        List<Product> productList = jdbcTemplate.query(sql, args, new BeanPropertyRowMapper<>(Product.class));
        return productList;
    }

    /**
     * 根据编码获取产品信息
     * @param number
     * @return
     */
    public List<Product> getByNumber(String number) {
        String sql = "select prd.*,brd.name 'brandName',stf.account 'createStaffAccount',pbc.pcount 'pbcPcount' from product prd  " +
                "join brand brd on prd.brand_id = brd.id left join staff stf on prd.create_staff_id = stf.id  " +
                "join pbc on prd.pbc_id = pbc.id where number = "+number;
        List<Product> productList = jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(Product.class));
        return productList;
    }
}
